<?php

namespace App\Services;

use App\Models\BaseModel;
use App\Models\PartCompany;
use App\Models\PartCompanyClassify;
use App\Models\SearchHistory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Query\Builder;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

/**
 * 案卷目录 服务层类
 * Class BookTitleService
 * @package App\Services
 */
class ProjectSearchService extends BaseService
{
    /**
     * 把项目中搜索的关键字进行保存
     *
     * @param $projectId
     * @param $keyword
     * @author: lxk
     * @date: 2024/5/7 13:13
     */
    public static function saveSearchKeyword($projectId, $keyword)
    {
        $info = SearchHistory::query()
            ->where('project_id', $projectId)
            ->where('name', $keyword)
            ->first();
        if (empty($info)) {
            $searchHistory = new SearchHistory();
            $searchHistory->name       = $keyword;
            $searchHistory->project_id = $projectId;
            $searchHistory->user_id    = user_id();
            $searchHistory->save();
        }
    }
    /**
     * 在项目中的案卷进行搜索
     * @author: lxk
     * @date: 2024/4/10 12:40
     */
    public function projectSearch($projectId, $perPage, $searchType, $keyword)
    {
        $partCompanyIds = PartCompany::query()->where('project_id', $projectId)->pluck('id')->toArray();
        $list = PartCompanyClassify::query()
            ->whereIn('part_company_id', $partCompanyIds)
            ->select('part_company_id', 'project_classify_id')
            ->get()
            ->toArray();
        $tables = [];
        foreach ($list as $val) {
            $table1 = "book_titles_{$val['part_company_id']}_{$val['project_classify_id']}";
            $table2 = "book_catalogs_{$val['part_company_id']}_{$val['project_classify_id']}";
            if (Schema::hasTable($table1) && Schema::hasTable($table2)) {
                if (DB::table($table1)->whereNull('deleted_at')->value('id')) {
                    $tables[] = $table1;
                }
            }
        }
        return  $this->getUnionBookTitleList($tables, $perPage, $searchType, $keyword);
    }

    /**
     * 获取多个数据表的案卷数据
     */
    public function getUnionBookTitleList($tables, $perPage, $searchType, $titleKeyword)
    {
        $queries = collect(); //定义一个集合
        foreach ($tables as $table) {
            if (Schema::hasTable($table)) {
                $tableParam = CommonService::getTableParams($table);
                $queries->push( //分表查询
                    DB::table($table)->from("$table as bt")
                        ->leftJoin('project_classifies as pc1', 'pc1.id', '=', 'bt.project_classify_id')
                        ->leftJoin('part_companies as pc2', 'pc2.id', '=', 'bt.part_company_id')
                        ->when($titleKeyword, function ($query) use ($tableParam, $searchType, $titleKeyword) {
                            $likeSearch = "%{$titleKeyword}%";
                            if ($searchType == 2) { //材料题名
                                $query->whereIn('bt.id', function (Builder $query) use ($tableParam, $likeSearch) {
                                    $catalogTable = 'book_catalogs_' . $tableParam['id1'] . '_' . $tableParam['id2'];
                                    $query->select('book_title_id')->from($catalogTable)->where("$catalogTable.title", 'like', $likeSearch);
                                });
                            } elseif ($searchType == 3) {
                                $query->where('bt.archives_no', 'like', $likeSearch);
                            } else {
                                $query->where('bt.title', 'like', $likeSearch);
                            }
                        })
                        ->whereNull('bt.deleted_at')
                        ->select(
                            'bt.id', 'bt.part_company_id', 'bt.project_classify_id',
                            'bt.serial_no', 'bt.archives_no', 'bt.title', 'bt.base_map',
                            'bt.blueprint', 'bt.word_material', 'bt.page_num', 'bt.store_period',
                            'bt.remark', 'bt.cabin_no', 'bt.volume', 'bt.years', 'bt.secret_level',
                            'bt.mixed_no', 'bt.start_date', 'bt.end_date', 'bt.problem', 'bt.status',
                            'pc1.title as pc_name', 'pc2.title as part_company_name')
                );
            }
        }
        $unionQuery = $queries->shift(); //出列一张表作为union的开始
        $queries->each(function ($item) use ($unionQuery) { //循环剩下的表添加union
            $unionQuery->unionAll($item);
        });
        if (empty($unionQuery)) return [];
        /**
         * with(new BaseModel())  new BaseModel()换成别的也可以目前不清楚如何使用的
         * 设置临时表的名称，添加临时表，顺序不能反过来，否则用关联约束会找不到表
         */
        $list = with(new BaseModel())->setTable('union_book_title')
            ->from(DB::raw("({$unionQuery->toSql()}) as union_book_title")) //添加临时表
            ->mergeBindings($unionQuery)
            ->orderBy('project_classify_id')
            ->orderBy('serial_no')
            ->paginate($perPage);

        if ($list->isEmpty()) return [];

        $list = $list->toArray();
        $page = request()->input('page', 1);
        foreach ($list['data'] as $key=>&$val) { //格式化起止日期格式为 20220910~20220910
            $bookCatalogInstance   = $this->getBookCatalogInstance($val['part_company_id'], $val['project_classify_id']);
            $val['number']         = ($page - 1) * $perPage + $key + 1;
            $val['start_end_date'] = format_date($val['start_date'], $val['end_date']);
            $val['data_count']     = $bookCatalogInstance->where('book_title_id', $val['id'])->count() ?: 0;
        }

        return $this->removeNoUsedPageParams($list);
    }

}
